package br.ufrj.dcc.xml.database;

public class QueryConstants {

	public static final boolean BENCHMARK = false;
	
	public static final String createDocTable = "CREATE TABLE documents ("
			+ " id SERIAL PRIMARY KEY, " + " nome VARCHAR(512) );";

	public static final String createItemTable = "CREATE TABLE items ("
			+ " data TEXT," + " l INTEGER," + " r INTEGER,"
			+ " type INTEGER,"
			+ " doc INTEGER REFERENCES documents (id) ON DELETE CASCADE );"
			+ " CREATE INDEX index_l ON items (l);"
			+ " CREATE INDEX index_doc ON items (doc);"
			+ " CREATE INDEX index_doc_l ON items (doc,l); ";
	public static final String createFunction =		
			"CREATE OR REPLACE FUNCTION func_getParent(integer, integer) \n" +
			"RETURNS integer AS' \n" +
			" DECLARE l_filho ALIAS FOR $1; \n" +
              " idDoc ALIAS FOR $2; \n" +
              " cont INT4; \n" +
              " x INT4; \n" +
              " l_irmao INT4; \n"+
              "	BEGIN \n" +
              "IF(l_filho = 0) THEN \n" +
              "RETURN -1; --é a raiz do documento. \n" +
              "END IF; \n" +
              "x := l_filho-1; \n"+
              "SELECT INTO cont count(*) \n"+
              "FROM items i WHERE i.l = x AND i.doc = idDoc; \n" +

              "IF  cont > 0 THEN \n"+
              	"RETURN x; \n" +
              "ELSE \n" +
              	" --logo, x é r do irmão. \n" +
              	" SELECT INTO l_irmao l FROM items i WHERE i.r = x AND i.doc = idDoc; \n" +
              	" RETURN (SELECT func_getParent(l_irmao, idDoc)); \n" +
              " END IF; \n" +
              "END; \n" +
              " 'LANGUAGE 'plpgsql'; \n";


	public static final String dropDocTable = "DROP TABLE documents;";

	public static final String dropItemTable = 
			" DROP INDEX index_l;" +
			" DROP INDEX index_doc;" +
			" DROP INDEX index_doc_l;" +
			" DROP TABLE items; ";

	public static final String insertDocSQL = "INSERT INTO documents"
			+ " (nome)" + " VALUES (?)";

	public static final String insertItemSQL = "INSERT INTO items"
			+ " (doc, l, r, type, data)" + " VALUES (?, ?, ?, ?, ?)";

	public static final String selectHigherDocId = "SELECT MAX(id) FROM documents;";

	public static final String selectFullDocumentSQL = "SELECT data, l, r, type FROM items "
			+ "WHERE doc = ? order by l;";

	public static final String selectFullNode = "SELECT data, l, r, type FROM items "
		+ "WHERE doc = ? AND l >= ? AND r <= ? order by l;";

	
	public static final String selectDocs = "SELECT id, nome FROM documents";

	public static final String deleteDoc = "DELETE FROM documents WHERE id = ? ";
}